package com.project.ToDone;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

/**
 * A class that helps interface with the SQLiteDatabse
 * 
 * @author Matthew Guzdial
 *
 */

public class DatabaseHelper extends SQLiteOpenHelper {
	static final String dbName="toDoneDBv6";
	static final String userTable="Users";
	static final String colUserID="UserID";
	static final String colName="UserRealName";
	static final String colUsername="Username";
	static final String colEmail="Email";
	static final String colPassword="Password";

	static final String eventTable="Events";
	static final String colID= "EventID";
	static final String colEventOwner="OwnerUsername";
	static final String colRecurring="Recurring";
	static final String colComplete="Complete";
	static final String colDate="EventDate";
	static final String colTime="EventTime";
	static final String colType="EventType";
	static final String colLocation="EventLocation";
	static final String colDescriptionS="EventDescriptionShort";
	static final String colDescriptionL="EventDescriptionLong";
	
	//static final String viewEmps="ViewEmps";
	
	static final int VERSION_NUMBER=3;
	
	private Context con;

	/**
	 * A constructor that just calls the super.
	 * Sets up the databse if it hasn't been created on
	 * a particular machine before and updates if the
	 * version number increases. 
	 * 
	 * @param context
	 */
	public DatabaseHelper(Context context) {
		//Change that final number to upgrade the databse
		  super(context, dbName, null,VERSION_NUMBER);
		  
		  con=context;
	}

	/**
	 * Creates the two tables (users and events), and sets up
	 * the specific id's for both as the 
	 * username for that specific user, 
	 * and for that user's events
	 * 
	 * 
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		db.execSQL("CREATE TABLE "+userTable+" ("+colUserID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+colName+ " TEXT, " +
			    colUsername+ " TEXT, "+colEmail+ " TEXT, "+colPassword+ " TEXT)");
		
		db.execSQL("CREATE TABLE "+eventTable+" ("+colID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
			    colRecurring+ " Integer, "+colComplete+ " Integer, "+colDate+ " TEXT, "+colTime+ " TEXT, "
				+colType+ " TEXT, "+colLocation+ " TEXT, "+colDescriptionS+ " TEXT, "+colDescriptionL+ " TEXT, "
				+colEventOwner+" TEXT)");
		/**
		db.execSQL("CREATE TRIGGER fk_empdept_deptid " +
			    " BEFORE INSERT "+
			    " ON "+eventTable+
			    
			    " FOR EACH ROW BEGIN"+
			    " SELECT CASE WHEN ((SELECT "+colUserID+" FROM "+userTable+" WHERE "
			    +colUserID+"=new."+colEventOwner+" ) IS NULL)"+
			    " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
			    "  END;");
		
		db.execSQL("CREATE VIEW "+viewEmps+
			    " AS SELECT "+eventTable+"."+colID+" AS _id,"+
			    " "+eventTable+"."+colRecurring+","+
			    " "+eventTable+"."+colComplete+","+
			    " "+eventTable+"."+colDate+","+
			    " "+eventTable+"."+colTime+","+
			    " "+eventTable+"."+colType+","+
			    " "+eventTable+"."+colLocation+","+
			    " "+eventTable+"."+colDescriptionS+","+
			    " "+eventTable+"."+colDescriptionL+","+
			    " "+userTable+"."+colUsername+""+
			    " FROM "+eventTable+" JOIN "+userTable+
			    " ON "+eventTable+"."+colEventOwner+" ="+userTable+"."+colUserID
			    );
		*/
			    	
	}
	
	/**
	 * On upgrade, wipes data and then calls
	 * create to rebuild the tables
	 * 
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS "+userTable);
		db.execSQL("DROP TABLE IF EXISTS "+eventTable);
		db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");
		onCreate(db);
	}
	
	/**
	 * Adds a specific user by breaking it into its
	 * component parts and placing each one into the table
	 * 
	 * @param user
	 */
	public void addUser(User user){
		
		
		SQLiteDatabase db=this.getWritableDatabase();
		ContentValues cv=new ContentValues();
		cv.put(colName, user.getName());
		cv.put(colUsername, user.getUsername());
		cv.put(colEmail, user.getEMail());
		cv.put(colPassword, user.getPassword());
		
		db.insert(userTable, colUsername, cv);
		db.close();
	}
	
	
	/**
	 * Takes a list of users and adds them to the database
	 * by repeatedly calling addUser for each one
	 * 
	 * @param users
	 */
	public void addUserList(ArrayList<User> users){
		for(User u: users){
			this.addUser(u);
		}
	}
	
	/**
	 * Adds an event for a specific user
	 * @param event
	 * @param user
	 */
	public void addEvent(Event event, User user){
		SQLiteDatabase db=this.getWritableDatabase();
		
		
		
		
		//This is just a test
		//db.execSQL("INSERT into Events" +
		//		"(Recurring, Complete, EventDate, EventTime, EventType, EventLocation, EventDescriptionShort, EventDescriptionLong, colEventOwner)" +
		//		"VALUES"+
		//		"(0,0,'12/12/12', '12:00', 'TEST', '1234 Location St Location, GA', 'This is a test.'");
		
		
		   ContentValues cv=new ContentValues();
		   //cv.put(colID, 1);
		   int recur =-1;
		   if(event.getRecurring()){
			   //1 is true
			   recur =1;
		   }
		   else{
			   //0 is false
			   recur=0;
		   }
		   cv.put(colRecurring, recur);
		   int complete=-1;
		   if(event.getCompleted()){
			   complete=1;
		   }
		   else{
			  complete=0;
		   }
		   cv.put(colComplete, complete);
		   
		   cv.put(colDate, event.getDate());
		   cv.put(colTime, event.getTime());
		   cv.put(colType, event.getType());
		   cv.put(colLocation, event.getLocation());
		   
		   cv.put(colDescriptionS, event.getDescriptionLong());
		   cv.put(colDescriptionL, event.getDescriptionShort());
		   cv.put(colEventOwner,user.getUsername());
		
		   
		
		   db.insert(eventTable, colID, cv);
		   db.close();
	}
	
	//Get some updates going on
	
	/**
	 * Updates the specified user, if that user has been edited
	 * 
	 * @param user
	 * @return
	 */
	public int UpdateUser(User user)
	  {
	   SQLiteDatabase db=this.getWritableDatabase();
	   ContentValues cv=new ContentValues();
	   cv.put(colName, user.getName());
	   cv.put(colUsername, user.getUsername());
	   cv.put(colEmail, user.getEMail());
	   cv.put(colPassword, user.getPassword());
	   return db.update(userTable, cv, colUsername+"=?", 
	    new String []{String.valueOf(user.getUsername())});   
	  }
	
	
	/**
	 * Updates an edited event
	 * 
	 * @param event
	 * @param user always the currUser, only their events can be edited
	 * @return
	 */
	public int UpdateEvent(Event event, User user)
	  {
	   SQLiteDatabase db=this.getWritableDatabase();
	   ContentValues cv=new ContentValues();
	   int recur =-1;
	   if(event.getRecurring()){
		   //1 is true
		   recur =1;
	   }
	   else{
		   //0 is false
		   recur=0;
	   }
	   cv.put(colRecurring, recur);
	   int complete=-1;
	   if(event.getCompleted()){
		   complete=1;
	   }
	   else{
		  complete=0;
	   }
	   cv.put(colComplete, complete);
	   
	   cv.put(colDate, event.getDate());
	   cv.put(colTime, event.getTime());
	   cv.put(colType, event.getType());
	   cv.put(colLocation, event.getLocation());
	   
	   
	   cv.put(colDescriptionL, event.getDescriptionShort());
	   cv.put(colDescriptionS, event.getDescriptionLong());
	   cv.put(colEventOwner,user.getUsername());
	   
	   int result=db.update(eventTable, cv, colID+"=?", 
			    new String []{String.valueOf(event.getID())});  

	   db.close();
	   return result;   
	  }
	
	
	//Deleting area
	
	/**
	 * WARNING: THIS DOESN'T WORK YET. 
	 * Deletes all events for a user
	 * 
	 * @param event
	 * @param user
	 */
	public void DeleteEvent(Event event, User user)
	  {
	   SQLiteDatabase db=this.getWritableDatabase();
	   db.delete(eventTable,colEventOwner+"=?", new String [] {String.valueOf(user.getUsername())});
	   db.close();
	  }
	
	//Getters for users and events based on a username
	public ArrayList<User> acquireUsers(){
		ArrayList<User> results = new ArrayList<User>();
		//Might not have done this correctly, try this first
		
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=db.rawQuery("SELECT "+colUsername+" as _id,"
				+colName+", "+colEmail+", "+colPassword
				+" from "+userTable,new String [] {});
		
    	
    	if (c != null ) {
    		if  (c.moveToFirst()) {
    			do {
    				String UserName = c.getString(c.getColumnIndex("_id"));
    				String Name = c.getString(c.getColumnIndex(colName));
    				String EMail = c.getString(c.getColumnIndex(colEmail));
    				String Password = c.getString(c.getColumnIndex(colPassword));
    				
    				User u = new User(Name, EMail, UserName, Password);
    				results.add(u);
    				
    			}while (c.moveToNext());
    		} 
    	}
    	db.close();
    	c.close();
    	return results;
	}
	
	/**
	 * Grabs all the events for a particular username (keyed
	 * under that username) and then returns them in an 
	 * arraylist of events
	 * 
	 * @param username
	 * @return
	 */
	
	public ArrayList<Event> getEventsByUsername(String username){
		ArrayList<Event> events = new ArrayList<Event>();
		SQLiteDatabase db=this.getReadableDatabase();
		//String [] columns=new String[]{"_id",colRecurring,colComplete,colDate, colTime, colType, colLocation, colDescriptionS, colDescriptionL, colUsername};
		//Cursor c=db.query(eventTable, columns, colUsername+"=?", new String[]{username}, null, null, null);
	
		 Cursor c=db.rawQuery("SELECT "+colID+" as _id,"+colRecurring+", "+colComplete+", "
		 +colDate+", "+colTime+", "+colType+", "+colLocation+", "+colDescriptionS+", "+colDescriptionL+" from "+eventTable+" WHERE "
		 +colEventOwner+"=?", new String []{username});
		
		//Cursor c=db.rawQuery("SELECT * FROM "+eventTable,new String [] {} );
		
		 if (c != null) {
	  		if  (c.moveToFirst()) {
	    			do {
	    				int id = c.getInt(0);
	    				
	    				int recur = c.getInt(1);
	    				boolean recurring =true;
	    				String recurrin="Recurring";
	    				
	    				if(recur==0){
	    					recurring=false;
	    					recurrin ="Not recurring";
	    				}
	    				int complete = c.getInt(2);
	    				
	    				boolean completed = true;
	    				String comp = "Completed";
	    				if(complete==0){
	    					comp="Incomplected";
	    					completed=false;
	    				}
	    				//This is correct
	    				String date = c.getString(3);
	    				
	    				//This is correct
	    				String time = c.getString(4);
	    				
	    				//This is correct
	    				String type = c.getString(5);
	    				
	    				//This is correct
	    				String location = c.getString(6);
	    				
	    				//This is correct
	    				String descriptionShort = c.getString(8); 
	    				
	    				//This is correct
	    				String descriptionLong = c.getString(7);
	    				
	    				
	    				
	    				Event event = new Event(recurring, completed, date, time, type, location, descriptionShort, descriptionLong);
	    				event.setID(id);
	    				events.add(event);
	    				
	    			}while (c.moveToNext());
	    		} 
	    	}
		db.close();
		c.close();
		return events;
	}
}
